/**
 *
 */
package org.starsource.forum.server.dao.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import java.util.LinkedHashSet;
import java.util.Set;
import java.util.NoSuchElementException;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import org.starsource.forum.client.exception.DatabaseException;
import org.starsource.forum.client.exception.SessionNotFoundException;
import org.starsource.forum.server.dao.CategoryDao;
import org.starsource.forum.server.dao.MessageDao;
import org.starsource.forum.server.dao.SessionDao;
import org.starsource.forum.server.dao.UserDao;
import org.starsource.forum.server.domain.Category;
import org.starsource.forum.server.domain.Session;

/**
 * @author james
 *
 */
public class SessionDaoJdbc implements SessionDao {

	private JdbcTemplate jdbcTemplate;

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}

	private UserDao userDao;
	public void setUserDao(UserDao userDao) {
		this.userDao = userDao;
	}

	private CategoryDao categoryDao;
	public void setCategoryDao(CategoryDao categoryDao) {
		this.categoryDao = categoryDao;
	}

	private MessageDao messageDao;
	public void setMessageDao(MessageDao messageDao) {
		this.messageDao = messageDao;
	}

	private static org.apache.log4j.Logger logger = org.apache.log4j.LogManager
	.getLogger(SessionDaoJdbc.class.getName());

	private class GroupRowCallbackHandler implements RowCallbackHandler {
		private Set<Session> sessionSet;

		public void processRow(ResultSet rs) throws SQLException {
			Session session = new Session();
			session.setId(rs.getInt("id"));
			session.setTitle(rs.getString("title"));
			session.setCategoryId(rs.getInt("category_id"));
			session.setOwnerId(rs.getInt("owner_id"));
			session.setCreateTimestamp(rs.getTimestamp("create_timestamp"));
			session.setUpdateTimestamp(rs.getTimestamp("update_timestamp"));

			sessionSet.add(session);
		}

		public GroupRowCallbackHandler(Set<Session> sessionSet) {
			this.sessionSet = sessionSet;
		}

	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.SessionDao#delete(org.starsource.forum.server.domain.Session)
	 */
	@Override
	public void delete(final Session session) throws DatabaseException {
		try {
			messageDao.delete(session);

			final String sql = "DELETE FROM t_session WHERE id = ?";
			jdbcTemplate.update(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					PreparedStatement ps = conn.prepareStatement(sql);
					ps.setInt(1, session.getId());
					return ps;
				}
			});
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		}
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.SessionDao#getSession(int)
	 */
	@Override
	public Session getSession(int id) throws SessionNotFoundException,
			DatabaseException {
		try {
			String sql = "SELECT * FROM t_session WHERE id = ? ";
			final Set<Session> sessionSet = new LinkedHashSet<Session>();
			jdbcTemplate.query(sql, new Object[] {id}, new GroupRowCallbackHandler(sessionSet));

			Session session = sessionSet.iterator().next();
			session.setCategory(categoryDao.getCategory(session.getCategoryId()));
			session.setOwner(userDao.getUser(session.getOwnerId()));
			session.setMessages(messageDao.getMessageSet(session));
			return session;
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		} catch (NoSuchElementException e) {
			logger.error(e.toString());
			throw new SessionNotFoundException();
		}
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.SessionDao#getSessionSet()
	 */
	@Override
	public Set<Session> getSessionSet() throws DatabaseException {
		String sql = "SELECT * FROM t_session ORDER BY update_timestamp DESC";
		final Set<Session> sessionSet = new LinkedHashSet<Session>();
		jdbcTemplate.query(sql, new GroupRowCallbackHandler(sessionSet));
		Iterator<Session> iterator = sessionSet.iterator();
		while (iterator.hasNext()) {
			Session session = iterator.next();
			session.setCategory(categoryDao.getCategory(session.getCategoryId()));
			session.setOwner(userDao.getUser(session.getOwnerId()));
//			session.setMessages(messageDao.getMessageSet(session));
		}

		return sessionSet;
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.SessionDao#getSessionSet(org.starsource.forum.server.domain.Category)
	 */
	@Override
	public Set<Session> getSessionSet(Category category)
			throws DatabaseException {
		try {
			String sql = "SELECT * FROM t_session WHERE category_id = ? ORDER BY update_timestamp DESC";
			final Set<Session> sessionSet = new LinkedHashSet<Session>();
			jdbcTemplate.query(sql, new Object[] {category.getId()}, new GroupRowCallbackHandler(sessionSet));
			Iterator<Session> iterator = sessionSet.iterator();
			while (iterator.hasNext()) {
				Session session = iterator.next();
				session.setCategory(categoryDao.getCategory(session.getCategoryId()));
				session.setOwner(userDao.getUser(session.getOwnerId()));
//				session.setMessages(messageDao.getMessageSet(session));
			}
			return sessionSet;
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		}
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.SessionDao#getSessionSet(java.util.Set)
	 */
	@Override
	public Set<Session> getSessionSet(Set<Category> categories)
			throws DatabaseException {
		try {

			Iterator<Category> iterator = categories.iterator();
			String categoryIdSet = "";
			while (iterator.hasNext()) {
				if (!"".equals(categoryIdSet)) {
					categoryIdSet += ",";
				}

				categoryIdSet += iterator.next().getId();
			}
			final String sql = "SELECT * FROM t_session WHERE category_id in (" + categoryIdSet + ") ORDER BY update_timestamp DESC";
			logger.debug("SQL: " + sql);
			final Set<Session> sessionSet = new LinkedHashSet<Session>();
			jdbcTemplate.query(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					PreparedStatement ps = conn.prepareStatement(sql);
					return ps;
				}
			}, new GroupRowCallbackHandler(sessionSet));

			Iterator<Session> sessionIterator = sessionSet.iterator();
			while (sessionIterator.hasNext()) {
				Session session = sessionIterator.next();
				session.setCategory(categoryDao.getCategory(session.getCategoryId()));
				session.setOwner(userDao.getUser(session.getOwnerId()));
//				session.setMessages(messageDao.getMessageSet(session));
			}
			return sessionSet;
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		}
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.SessionDao#save(org.starsource.forum.server.domain.Session)
	 */
	@Override
	public void save(final Session session) throws DatabaseException {
		final String sql = "INSERT INTO t_session(title, category_id, owner_id, create_timestamp, update_timestamp) VALUES(?,?,?,?,?)";
		KeyHolder keyHolder = new GeneratedKeyHolder();
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);

				ps.setString(1, session.getTitle());
				ps.setInt(2, session.getCategoryId());
				ps.setInt(3, session.getOwnerId());
				ps.setTimestamp(4, session.getCreateTimestamp());
				ps.setTimestamp(5, session.getUpdateTimestamp());

				return ps;
			}
		}, keyHolder);
		session.setId(keyHolder.getKey().intValue());
	}

	/* (non-Javadoc)
	 * @see org.starsource.forum.server.dao.SessionDao#update(org.starsource.forum.server.domain.Session)
	 */
	@Override
	public void update(final Session session) throws DatabaseException {
		final String sql = "UPDATE t_session set title = ?, category_id = ?, owner_id = ?, create_timestamp = ?, update_timestamp = ?  WHERE id = ?";
		jdbcTemplate.update(new PreparedStatementCreator() {
			public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
				PreparedStatement ps = conn.prepareStatement(sql);
				ps.setString(1, session.getTitle());
				ps.setInt(2, session.getCategoryId());
				ps.setInt(3, session.getOwnerId());
				ps.setTimestamp(4, session.getCreateTimestamp());
				ps.setTimestamp(5, session.getUpdateTimestamp());
				ps.setInt(6, session.getId());
				return ps;
			}
		});
	}

	@Override
	public Set<Session> getSessionSetByKeywords(String[] keywords)
			throws DatabaseException {
		try {

			String condition = "";
			for(int index = 0; index < keywords.length; index ++) {
				if (index > 0) {
					condition += " OR ";
				}
				condition += "title LIKE '%" + keywords[index] + "%'";
			}

			String whereClause = "";
			if (!"".equals(condition)) {
				whereClause = " WHERE " + condition;
			}
			final String sql = "SELECT * FROM t_session " + whereClause + " ORDER BY update_timestamp DESC";
			logger.debug("SQL: " + sql);
			final Set<Session> sessionSet = new LinkedHashSet<Session>();
			jdbcTemplate.query(new PreparedStatementCreator() {
				public PreparedStatement createPreparedStatement(Connection conn) throws SQLException {
					PreparedStatement ps = conn.prepareStatement(sql);
					return ps;
				}
			}, new GroupRowCallbackHandler(sessionSet));

			Iterator<Session> sessionIterator = sessionSet.iterator();
			while (sessionIterator.hasNext()) {
				Session session = sessionIterator.next();
				session.setCategory(categoryDao.getCategory(session.getCategoryId()));
				session.setOwner(userDao.getUser(session.getOwnerId()));
//				session.setMessages(messageDao.getMessageSet(session));
			}
			return sessionSet;
		} catch (DataAccessException e) {
			logger.error(e.toString());
			throw new DatabaseException();
		}
	}

}
